package com.ai.platform.common.util;

import com.ai.platform.common.page.*;
import org.apache.commons.collections.MapUtils;
import org.yaml.snakeyaml.Yaml;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.net.URL;
import java.sql.*;
import java.util.*;

/**
 * Created by yeyingsheng on 2018/4/14.
 * 数据库操作工具类
 */
public class JDBCUtil {

    //驱动
    private String driverClassName;

    //url
    private String url;

    //用户名
    private String username;

    //密码
    private String password;

    // 定义数据库的链接
    private Connection connection;

    // 定义sql语句的执行对象
    private PreparedStatement pstmt;

    // 定义查询返回的结果集合
    private ResultSet resultSet;

    public JDBCUtil(){

    }

    /**
     * 默认连接创建
     * @return
     */
    public JDBCUtil defaultBuilder(){
        Yaml yaml = new Yaml();
        URL ymlUrl = JDBCUtil.class.getClassLoader().getResource("application.yml");
        if (ymlUrl != null) {
            Map map = null;
            try {
                map = yaml.load(new FileInputStream(ymlUrl.getFile()));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            LinkedHashMap springMap= (LinkedHashMap) map.get("spring");
            if(null!=springMap){
                LinkedHashMap datasourceMap= (LinkedHashMap) springMap.get("datasource");
                if(null!=datasourceMap){
                    this.driverClassName= MapUtils.getString(datasourceMap,"driver-class-name");
                    this.url=MapUtils.getString(datasourceMap,"url");
                    this.username=MapUtils.getString(datasourceMap,"username");
                    this.password=MapUtils.getString(datasourceMap,"password");
                }
            }
        }
        this.connection=getConnection();
        return this;
    }

    public JDBCUtil(String driverClassName, String url, String username, String password) {
        this.driverClassName = driverClassName;
        this.url = url;
        this.username = username;
        this.password = password;
        this.connection=getConnection();
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     */
    private Connection getConnection() {
        try {
            Class.forName(this.driverClassName); // 注册驱动
            this.connection = DriverManager.getConnection(this.url, this.username, this.password); // 获取连接
        } catch (Exception e) {
            throw new RuntimeException("get connection error!", e);
        }
        return connection;
    }

    /**
     * 创建一个连接
     * @return
     */
    public Connection createConnection() {
        try {
            if(null==connection || connection.isClosed()) {
                Class.forName(driverClassName); // 注册驱动
                connection = DriverManager.getConnection(url, username, password); // 获取连接
            }
        } catch (Exception e) {
            throw new RuntimeException("get connection error!", e);
        }
        return connection;
    }

    /**
     * 执行更新操作
     *
     * @param sql
     *            sql语句
     * @param params
     *            执行参数
     * @return 执行结果
     * @throws SQLException
     */
    public boolean executeUpdate(String sql, List<?> params)
            throws SQLException {

        boolean flag = false;
        try {
            int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            // 填充sql语句中的占位符
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            result = pstmt.executeUpdate();
            flag = result > 0 ? true : false;
            return flag;
        }finally {
            releaseConn();
        }
    }

    /**
     * 执行查询操作
     *
     * @param sql
     *            sql语句
     * @param params
     *            执行参数
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> executeQuery(String sql, List<?> params) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            int index = 1;
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            list=resultSetList(resultSet);
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            releaseConn();
        }
        return list;
    }


    /**
     * 执行分页查询操作
     *
     * @param sql
     *            sql语句
     * @param params
     *            执行参数
     * @return
     * @throws SQLException
     */
    public Pagination executeQueryByPage(String sql, List<?> params) {
        PageContext context=PageContext.getContext();
        try {
            int index = 1;
            if(null==context || !context.isPagination()){
                return context;
            }
            StringBuffer countSql  = new StringBuffer(sql.length()+100);
            countSql.append("select count(1) from (").append(sql).append(") as total");
            pstmt = connection.prepareStatement(countSql.toString());
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            if(resultSet.next()){
                context.setTotalRows(resultSet.getInt(1));
            }
            if(context.getTotalRows()==0){
                return context;
            }
            Dialect dialect= Dialect.createDialect(connection);
            String boundSql=sql;
            if(dialect instanceof MysqlDialect){
                MysqlDialect mysqlDialect= (MysqlDialect) dialect;
                boundSql=mysqlDialect.getLimitString(sql,(context.getPageNow()-1)*context.getPageSize(),context.getPageSize());
            }else if(dialect instanceof OracleDialect){
                OracleDialect oracleDialect= (OracleDialect) dialect;
                boundSql=oracleDialect.getLimitString(sql,(context.getPageNow()-1)*context.getPageSize(),context.getPageSize());
            }
            pstmt = connection.prepareStatement(boundSql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            context.setData(resultSetList(resultSet));
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            releaseConn();
        }
        return context;
    }

    /**
     * 设置结果集
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private List<Map<String, Object>> resultSetList(ResultSet resultSet) throws SQLException{
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        List<Map<String, Object>> list = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < cols_len; i++) {
                int curIndex=i + 1;
                String cols_name = metaData.getColumnName(curIndex);
                Object cols_value = resultSet.getObject(curIndex);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 释放资源
     */
    private void releaseConn() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
